
CREATE  proc sp_close_transit_lots (

@lot_number varchar(50),
@part_number varchar(50), 
@ps_type varchar(50),
@vendor_name varchar(50),
@ship_date datetime,
@ship_qty int)

as
begin

	declare @lot_id int
  	declare @process_step_id int
  	declare @location_id int	
	
	if @ship_date = ''
	begin
		set @ship_date = null	
	end

	select @location_id = Location_ID from Location where L_Type = @ps_type and L_Vendor_Name = @vendor_name
	select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and PS_Type_Name = @ps_type and product.ProcessStep_ID = Process_Step.ProcessStep_ID 
	if @process_step_id is null -- new Product or Product Name mismatch
  	begin
	
		select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name=@ps_type and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID and Product_Map_Table.Location_ID = @location_id	
		if @process_step_id is null
  		begin
		
			-- dummy update to avoid SQLException
			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
			return
		end
	end
	select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number	
	if @ship_date is null
	begin
		
		Update shipped_lot set Status = 'Close', Ship_Status = 1 where Lot_Id = @lot_id and Ship_Qty = @ship_qty 
	end
	else
	begin
		
		Update shipped_lot set Status = 'Close', Ship_Status = 1 where Lot_Id = @lot_id and Ship_Date = @ship_date and Ship_Qty = @ship_qty 
	end
	
end


